package com.lyouu.dataminer.bustour.datatransfer;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.factory.DaoFactory;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.model.TourCity;
import com.lyouu.dataminer.model.TourCountry;
import com.lyouu.dataminer.model.TourViewpoint;
import com.lyouu.dataminer.util.TourMap;
import com.lyouu.dataminer.xmlhandler.TourMapXmlParser;

public class MdSubjectBustourTransfer {
	private Logger logger = Logger.getLogger(MdSubjectBustourTransfer.class);

	public MdSubjectBustourTransfer() {

	}

	public void changePriceType() throws SQLException {
		Connection conn1 = MysqlDbFactory
				.getConnection(DbConstants.DB_LYOUU_BAK);
		ArrayList<MdSubjectBustourRow> rows = new ArrayList<MdSubjectBustourRow>();
		String sql = "SELECT sid,c_price,c_childprice,c_5groupprice FROM md_subject_bustour WHERE sid<83";
		Statement stmt = conn1.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			rows.add(new MdSubjectBustourRow(rs.getInt(1), rs.getString(2), rs
					.getString(3), rs.getString(4)));
		}
		conn1.close();
		Connection conn2 = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		sql = "UPDATE md_subject_bustour SET c_price=?, c_childprice=?,c_5groupprice=?,c_priceunit='欧元' WHERE sid=?";
		PreparedStatement prestmt = conn2.prepareStatement(sql);
		String price;

		for (MdSubjectBustourRow row : rows) {
			logger.info("sid=" + String.valueOf(row.getSid()));
			// normal price
			price = row.getPrice();
			price = price.replaceAll("欧元 起", "");
			prestmt.setBigDecimal(1, BigDecimal.valueOf(Double.valueOf(price)));
			// child price
			price = row.getChildprice();
			price = price.replaceAll("欧元 起", "");
			if (price.isEmpty())
				prestmt.setBigDecimal(2, BigDecimal.valueOf(0.00));
			else {
				logger.warn(row.getChildprice());
				prestmt.setBigDecimal(2,
						BigDecimal.valueOf(Double.valueOf(price)));
			}
			// group price
			price = row.getGroupprice();
			price = price.replaceAll("欧元 起", "");
			if (price.isEmpty())
				prestmt.setBigDecimal(2, BigDecimal.valueOf(0.00));
			else {
				logger.warn(row.getGroupprice());
				prestmt.setBigDecimal(3,
						BigDecimal.valueOf(Double.valueOf(price)));
			}
			prestmt.setInt(4, row.getSid());
			prestmt.executeUpdate();
		}
		conn2.close();
	}

	private class MdSubjectBustourRow {
		int sid;
		String price, childprice, groupprice;

		public MdSubjectBustourRow(int sid, String price, String childprice,
				String groupprice) {
			this.sid = sid;
			this.price = price;
			this.childprice = childprice;
			this.groupprice = groupprice;
		}

		public int getSid() {
			return sid;
		}

		public void setSid(int sid) {
			this.sid = sid;
		}

		public String getPrice() {
			return price;
		}

		public void setPrice(String price) {
			this.price = price;
		}

		public String getChildprice() {
			return childprice;
		}

		public void setChildprice(String childprice) {
			this.childprice = childprice;
		}

		public String getGroupprice() {
			return groupprice;
		}

		public void setGroupprice(String groupprice) {
			this.groupprice = groupprice;
		}
	}

	public void updateDestCountry() throws SQLException {
		ArrayList<TourCountry> countryList = new ArrayList<TourCountry>();
		ArrayList<TourCity> cityList = new ArrayList();
		TourMapXmlParser.parse(countryList, cityList);

		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		String sql = "select * FROM md_subject_bustour";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			DaoFactory.getMdSubjectBustourDao().updateDestCountry(
					rs.getInt("sid"),
					TourMap.getInstance().findDestCountries(rs.getString("c_routine")));
		}
		conn.close();
	}

	public static void main(String[] args) {
		try {
			// new MdSubjectBustourTransfer().changePriceType();
			new MdSubjectBustourTransfer().updateDestCountry();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
